<meta charset="utf-8" >
<?php
/** 
* make.haystack_supplies.php
*
* select, transaction:InnoDB,     insert, update:MyISAM 
*
*/

require_once('../../common/_init.php');
require_once('../../common/_function.php');
require_once(CLASS_PATH."/__autoload.php");

$con     	 = new P79DAO;
$table_name  = 'haystack_supplies';
$exist_table = $con->db_numrows($con->is_table($table_name));


//-------------- 기존 데이터 테이블 백업
if(isset($exist_table))
{
	$str = date("Ymd");
	$qry = "RENAME TABLE `haystack_supplies` TO `z_{$str}_haystack_supplies`" ;
	$result = $con->db_query($qry, 'utf-8');
	
	if($result == true)   
		echo "Table : z_{$str}_haystack_supplies backup OK!!";	
}


//-------------- 새로운 테이블 생성
//if(empty($exist_table))
//{
   $qry = "CREATE TABLE IF NOT EXISTS `haystack_supplies` (
		  `sSerial` char(8) NOT NULL COMMENT '소모품 일련번호',
		  `ssName` varchar(64) NOT NULL DEFAULT ' ' COMMENT '공백&특수문자 제거한 data',
		  `spSerial` varchar(255) NOT NULL COMMENT '사용프린터',
		  `hits` tinyint(6) NOT NULL DEFAULT '0' COMMENT '최근 한달 검색 횟수',
		  PRIMARY KEY (`sSerial`)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='소모품 검색 키워드 base'";

   $con->db_query($qry, 'utf-8');
//}


//-------------- 새로운 데이터 구성
	
$start = get_time();


$q 		  = "SELECT sSerial, sName FROM list_supplies";
$r 		  = $con->db_query($q);
$cnt_lp   = mysqli_num_rows($r);	

echo "<br /> Working Records: ".$cnt_lp."<br />";	

$pattern_string = "/[^A-Za-z]/";
$pattern_digit 	= "/[^0-9]/";
$pattern_normal = "/[^A-Za-z0-9]/";
$arrQry  = array();

while($row = mysqli_fetch_array($r, MYSQLI_ASSOC))
{
   $sSerial = $row["sSerial"];
   $sName   = $row["sName"];
   $sName   = preg_replace($pattern_normal, "", $sName);
   $ssName  = strtolower($sName);   
   $arrQry[] = "('$sSerial', '$ssName')";
}  
$qry = join(',', $arrQry);

$qy  = "INSERT INTO haystack_supplies(sSerial, ssName) VALUES ";
$qy .= $qry;
$insertOK = $con->db_query($qy);


$end = get_time();
$lead_time = $end - $start;	
echo "Lead Time: ".$lead_time."sec.<br />";	

function get_time() 
{
	list($sec, $microsec) = explode(' ', microtime());
	return ((float) $sec + (float) $microsec);
}

//-------------------------

$qr      = "SELECT pSerial, sSerial FROM list_link ORDER BY sSerial";
$rr      = $con->db_query($qr);
$count   = 0;
$sSerial = null;
$str     = array();

while($row = mysqli_fetch_array($rr, MYSQLI_ASSOC))
{
	if(isset($sSerial) && $sSerial != $row["sSerial"]) 
	{		
		$str = array_unique($str);		
		$str = implode(' ', $str);
		$str = trim($str);	
	
		$qrr = "UPDATE haystack_supplies SET spSerial = CONCAT(spSerial, ' $str' ) WHERE (sSerial = '$sSerial') ";
		$rrr = $con->db_query($qrr);	
		$str = null;
	}

	$sSerial = $row["sSerial"];	
	$str[]   = $row["pSerial"];	
}

$end2       = get_time();
$lead_time2 = $end2 - $start;	
echo "Lead Time2: ".$lead_time2."sec.<br />";	

// 'spSerial' 필드의 값을 sort하여 정렬하면 보다 빠른 검색가능??


?>